using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;

/// <summary>
/// Summary description for UserBll
/// </summary>
public class UserBll
{
    private const string SQL_SELECT_USER = "SELECT * FROM USERLOGIN ORDER BY ID DESC";
    private const string SQL_SELECT_USER_BYID = "SELECT * FROM USERLOGIN WHERE ID = @ID";
    private const string SQL_CHECK_USER = "SELECT * FROM USERLOGIN WHERE USERNAME = @USERNAME " + " AND PASSWORD = @PASSWORD";
    private const string SQL_DELETE_USER = "DELETE FROM USERLOGIN WHERE ID = @ID";
    private const string SQL_INSERT_USER = "INSERT INTO USERLOGIN(USERNAME, PASSWORD) VALUES (@USERNAME, @PASSWORD)";
    private const string SQL_UPDATE_USER = "UPDATE USERLOGIN SET USERNAME = @USERNAME, PASSWORD = @PASSWORD";
    private const string SQL_COUNT = "SELECT COUNT(*) FROM USERLOGIN";
    private const string PARM_USERNAME = "@USERNAME";
    private const string PARM_PASSWORD = "@PASSWORD";
    private const string PARM_USER_ID = "@ID";
	
    public UserBll()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    private User UserManipulate(SqlDataReader reader)
    {
        User u = new User();
        if (!reader.IsDBNull(0))
            u.Id = reader.GetInt32(0);
        if (!reader.IsDBNull(1))
            u.UserName = reader.GetString(1);
        if (!reader.IsDBNull(2))
            u.Password = reader.GetString(2);
        return u;
    }

    public IList<User> GetAll()
    {
        IList<User> result = new List<User>();
        using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
            CommandType.Text, SQL_SELECT_USER, null))
        {
            while (reader.Read())
            {
                User u = UserManipulate(reader);
                result.Add(u);
            }
        }
        return result;
    }

    public User CheckUser(string username, string password)
    {
        User u = null;
        SqlParameter[] parms;
        parms = new SqlParameter[] { 
            new SqlParameter(PARM_USERNAME, SqlDbType.VarChar),
            new SqlParameter(PARM_PASSWORD, SqlDbType.VarChar)};
        parms[0].Value = username;
        parms[1].Value = password;

        using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_CHECK_USER, parms))
        {
            if (reader.Read())
            {
                u = UserManipulate(reader);
            }
        }
        return u;
    }

    public bool Delete(int id)
    {
        SqlParameter[] parms;
        parms = new SqlParameter[] { new SqlParameter(PARM_USER_ID, SqlDbType.Int) };
        parms[0].Value = id;

        try
        {
            int result = 0;
            result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_DELETE_USER, parms);
            return (result > 0 ? true : false);
        }
        catch (Exception e)
        {
            throw e;
        }
        return false;
    }

    public bool Update(User u)
    {
        SqlParameter[] parms;
        parms = new SqlParameter[] {
            new SqlParameter(PARM_USER_ID, SqlDbType.Int),
            new SqlParameter(PARM_USERNAME, SqlDbType.NVarChar),
            new SqlParameter(PARM_PASSWORD, SqlDbType.NVarChar)};
        parms[0].Value = u.Id;
        parms[1].Value = u.UserName;
        parms[2].Value = u.Password;

        try
        {
            int result = 0;
            result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_UPDATE_USER, parms);
            return (result > 0 ? true : false);
        }
        catch (Exception e)
        {
            throw e;
        }
        return false;
    }

    public bool Insert(User u)
    {
        SqlParameter[] parms;
        parms = new SqlParameter[] {
            new SqlParameter(PARM_USER_ID, SqlDbType.Int),
            new SqlParameter(PARM_USERNAME, SqlDbType.NVarChar),
            new SqlParameter(PARM_PASSWORD, SqlDbType.NVarChar)};
        parms[0].Value = u.Id;
        parms[1].Value = u.UserName;
        parms[2].Value = u.Password;

        try
        {
            int result = 0;
            SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
            result = SQLDBHelper.ExecuteNonQuery(conn,
                CommandType.Text, SQL_INSERT_USER, parms);
            if (conn.State == ConnectionState.Open)
                conn.Close();
            return (result > 0 ? true : false);
        }
        catch (Exception e)
        {
            throw e;
        }
        return false;
    }

    public int CountAccount()
    {
        try
        {
            int result = (int)SQLDBHelper.ExecuteScalar(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_COUNT, null);
            return result;
        }
        catch (Exception e)
        {
            throw e;
        }
        return 0;
    }

    public User GetUserByID(int uID)
    {
        SqlParameter[] parms;
        parms = new SqlParameter[] { new SqlParameter(PARM_USER_ID, SqlDbType.Int) };
        parms[0].Value = uID;

        using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
            CommandType.Text, SQL_SELECT_USER_BYID, parms))
        {
            if (reader.Read())
            {
                User u = UserManipulate(reader);
                return u;
            }
        }
        return null;
    }
}
